小编典典

在 PostgreSQL 中发现表的行数的快速方法

all

我需要知道表格中的行数来计算百分比。如果总计数大于某个预定义的常量,我将使用常量值。否则,我将使用实际的行数。

我可以使用SELECT count(*) FROM table. 但是如果我的常量值为 500,000 并且我的表中有
5,000,000,000 行,那么计算所有行将浪费大量时间。

一旦超过我的恒定值,是否可以停止计数?

只要它低于给定的限制,我就需要确切的行数。否则,如果计数超过限制,我将使用限制值并希望尽快得到答案。

像这样的东西:

SELECT text,count(*), percentual_calculus()  
FROM token  
GROUP BY text  
ORDER BY count DESC;

阅读 462

收藏
2022-07-29

共1个答案

小编典典

众所周知,在 PostgreSQL
中计算大表中的行数很慢。MVCC模型需要完整的活动行数才能获得精确的数字。如果计数不必
与* 您的情况 完全相同 ,则有一些解决方法可以 显着加快这一速度。 ***

(请记住,在并发写入负载下,即使是“精确”计数也可能在到达时失效。)

准确计数

大桌子慢
使用并发写操作,它可能在你得到它的那一刻就已经过时了。

SELECT count(*) AS exact_count FROM myschema.mytable;
估计

极快: _

SELECT reltuples AS estimate FROM pg_class where relname = 'mytable';

通常,估计值非常接近。多接近,取决于是否ANALYZE运行VACUUM足够 - 其中“足够”由表的写入活动级别定义。

更安全的估计

以上忽略了在一个数据库中具有相同名称的多个表的可能性 - 在不同的模式中。为了解决这个问题:

SELECT c.reltuples::bigint AS estimate
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relname = 'mytable'
AND    n.nspname = 'myschema';

演员可以很好地bigint格式化real数字,特别是对于大计数。

更好的估计

SELECT reltuples::bigint AS estimate
FROM   pg_class
WHERE  oid = 'myschema.mytable'::regclass;

更快、更简单、更安全、更优雅。请参阅有关对象标识符类型的手册。

在 Postgres 9.4+
中替换'myschema.mytable'::regclassto_regclass('myschema.mytable')不获取任何内容,而不是获取无效表名的异常。看:

更好的估计(只需很少的额外成本)

我们可以做 Postgres 规划器所做的事情。引用手册中的 _ 行估计示例_

这些数字是最新的VACUUMANALYZE在桌子上的。然后,规划器获取表中实际的当前页数(这是一个廉价的操作,不需要表扫描)。relpages如果这与then不同,则reltuples相应地缩放以得出当前的行数估计。

Postgres 使用estimate_rel_size定义的 in
src/backend/utils/adt/plancat.c,它还涵盖了没有数据 in 的极端情况,pg_class因为关系从未被清理过。我们可以在 SQL 中做类似的事情:

最小形式

SELECT (reltuples / relpages * (pg_relation_size(oid) / 8192))::bigint
FROM   pg_class
WHERE  oid = 'mytable'::regclass;  -- your table here

安全明确

SELECT (CASE WHEN c.reltuples < 0 THEN NULL       -- never vacuumed
             WHEN c.relpages = 0 THEN float8 '0'  -- empty table
             ELSE c.reltuples / c.relpages END
     * (pg_catalog.pg_relation_size(c.oid)
      / pg_catalog.current_setting('block_size')::int)
       )::bigint
FROM   pg_catalog.pg_class c
WHERE  c.oid = 'myschema.mytable'::regclass;      -- schema-qualified table here

不会与空表和从未见过的表中断VACUUMANALYZE手册pg_class

如果该表尚未被清理或分析,则reltuples包含-1指示行数未知。

如果此查询返回,则为表NULL运行ANALYZEor并重复。VACUUM(或者,您可以像 Postgres
那样根据列类型估计行宽,但这很乏味且容易出错。)

如果此查询返回0,则该表似乎是空的。但我会ANALYZE确定。(也许检查你的autovacuum设置。)

通常block_size
8192。current_setting('block_size')::int涵盖罕见的例外情况。

表和模式限定使其不受任何search_path和范围的影响。

无论哪种方式,查询对我来说始终花费 < 0.1 ms。

更多网络资源:

[TABLESAMPLE SYSTEM (n)](https://www.postgresql.org/docs/current/sql-

select.html#SQL-FROM)在 Postgres 9.5+

SELECT 100 * count(*) AS estimate FROM mytable TABLESAMPLE SYSTEM (1);

就像@a_horse 评论的那样,如果由于某种原因统计信息不够最新,则为命令添加的子句SELECT可能很有用。pg_class例如:

  • 没有autovacuum跑步。
  • 紧接着大INSERT// UPDATEDELETE
  • TEMPORARY表(不包括在内autovacuum)。

这仅查看随机 n % (1在示例中)选择的块并计算其中的行数。更大的样本会增加成本并减少错误,您的选择。准确性取决于更多因素:

  • 行大小的分布。如果给定的块恰好比通常的行更宽,则计数低于通常等。
  • 死元组或FILLFACTOR每块占用空间。如果整个表格分布不均,估计值可能会偏离。
  • 一般舍入误差。

通常,来自的估计pg_class会更快、更准确。

回答实际问题

首先,我需要知道该表中的行数,如果总计数大于某个预定义的常量,

以及是否…

…可能在计数通过我的常量值的那一刻,它将停止计数(而不是等待完成计数以通知行数更大)。

是的。 您可以使用 子查询LIMIT

SELECT count(*) FROM (SELECT 1 FROM token LIMIT 500000) t;

Postgres 实际上停止 超出给定限制的计数,您可以获得最多 n 行(在示例中为 500000)的 准确和当前 计数,否则为 n 。
不过,它的速度几乎没有 中的估计值那么快。 pg_class

2022-07-29